#!/usr/bin/python3

import os
import pymysql
import pyecharts.options as opts
from pyecharts.charts import Line, Grid

##connect info
mtsql_host = os.getenv('MTSQL_METDATA_HOST')
mtsql_port_str = os.getenv('MTSQL_METDATA_PORT')
mtsql_port = int(mtsql_port_str)
mtsql_user = os.getenv('MTSQL_METDATA_USER')
mtsql_password = os.getenv('MTSQL_METDATA_PASSWORD')
mtsql_database = os.getenv('MTSQL_METDATA_DATABASE')
mtsql_charset = os.getenv('MTSQL_METDATA_CHARSET')

##test info
mtsql_pt_result_graph_path=os.getenv('MTSQL_PT_RESULT_GRAPH_PATH')
mtsql_pt_feature = os.getenv('MTSQL_PT_FEATURE')
mtsql_pt_with_feature = os.getenv('MTSQL_PT_WITH_FEATURE')
mtsql_pt_test_scenarios = os.getenv('MTSQL_PT_TEST_SCENARIOS')
mtsql_pt_sub_test_scenarios = os.getenv('MTSQL_PT_SUB_TEST_SCENARIOS')
mtsql_pt_db_type = os.getenv('MTSQL_PT_DB_TYPE')
mtsql_pt_test_case = os.getenv('MTSQL_PT_TEST_CASE')
mtsql_pt_test_batch_no = os.getenv('MTSQL_PT_TEST_BATCH_NO')
mtsql_pt_os_name = os.getenv('MTSQL_PT_OS_NAME')
mtsql_pt_machine_hardware_name = os.getenv('MTSQL_PT_MACHINE_HARDWARE_NAME')
mtsql_pt_deploy_type = os.getenv('MTSQL_PT_DEPLOY_TYPE')
mtsql_pt_deploy_type = mtsql_pt_deploy_type.lower()

#loop
mtsql_pt_db_types= ['mtsql0', 'mtsql1', 'mariadb', 'alisql', 'txsql']

#key name
mtsql_latency_min_name='latency_max'
mtsql_latency_avg_name='latency_max'
mtsql_latency_max_name='latency_max'
mtsql_percentile_name='percentile'


db= pymysql.connect(host=mtsql_host, port=mtsql_port, user=mtsql_user, password=mtsql_password, database=mtsql_database, charset=mtsql_charset)

cursor = db.cursor()

sql= "SELECT threads,value " \
     "FROM pt_feature_result_info " \
     "where feature = %s " \
     "and with_feature= %s " \
     "and test_scenarios= %s " \
     "and sub_test_scenarios= %s " \
     "and db_type= %s " \
     "and test_case= %s " \
     "and test_batch_no= %s " \
     "and os_name= %s " \
     "and machine_hardware_name= %s " \
     "and deploy_type= %s " \
     "and name= %s " \
     "order by CAST(threads as UNSIGNED)"

#common
graph_file_name = mtsql_pt_result_graph_path \
                  + "/pt_result_latency_summary_by_test_case_" \
                  + mtsql_pt_deploy_type \
                  + "_" \
                  + mtsql_pt_feature \
                  + "_" \
                  + mtsql_pt_with_feature \
                  + "_" \
                  + mtsql_pt_test_scenarios \
                  + mtsql_pt_test_batch_no \
                  + "_" \
                  + mtsql_pt_test_case \
                  + ".html"

# percentile_line = Line("percentile_summary_by_test_case","percentile_summary_by_test_case")
# latency_max_line = Line("latency_max_summary_by_test_case","latency_max_summary_by_test_case")
# latency_avg_line = Line("latency_avg_summary_by_test_case","latency_avg_summary_by_test_case")
# latency_min_line = Line("latency_min_line_summary_by_test_case","latency_min_line_summary_by_test_case")
percentile_line = Line()
latency_max_line = Line()
latency_avg_line = Line()
latency_min_line = Line()
percentile_line_max_global=0
percentile_line_min_global=0
latency_max_line_max_global=0
latency_max_line_min_global=0
latency_avg_line_max_global=0
latency_avg_line_min_global=0
latency_min_line_max_global=0
latency_min_line_min_global=0

for mtsql_pt_db_type in mtsql_pt_db_types:

    try:
        print("result latency summary_by_test_case graph for db_type=", mtsql_pt_db_type)
        mtsql_pt_db_type_full=mtsql_pt_db_type
        if mtsql_pt_db_type == 'mtsql0' :
            mtsql_pt_with_feature='0'
            mtsql_pt_db_type='mtsql'
        elif mtsql_pt_db_type == 'mtsql1' :
            mtsql_pt_with_feature='1'
            mtsql_pt_db_type='mtsql'
        else:
            mtsql_pt_with_feature='1'

        ##query percentile info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_percentile_name)
        mtsql_version_list = []
        mtsql_percentile_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_percentile_list.append(value_float)
        # graph
        print("result graph for percentile!")
        percentile_line_max = max(mtsql_percentile_list)
        percentile_line_max_global = max(percentile_line_max, percentile_line_max_global)

        percentile_line.add_xaxis(xaxis_data=mtsql_version_list)
        percentile_line.add_yaxis(
            series_name=mtsql_pt_db_type_full,
            y_axis=mtsql_percentile_list,
            markpoint_opts=opts.MarkPointOpts(
                data=[
                    opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                    opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                ]
            ),
            is_smooth=True,
        )

        ##query latency_max info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_latency_max_name)
        mtsql_version_list = []
        mtsql_latency_max_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_latency_max_list.append(value_float)
        # graph
        print("result graph for latency_max!")
        latency_max_line_max = max(mtsql_latency_max_list)
        latency_max_line_max_global = max(latency_max_line_max, latency_max_line_max_global)

        latency_max_line.add_xaxis(xaxis_data=mtsql_version_list)
        latency_max_line.add_yaxis(
            series_name=mtsql_pt_db_type_full,
            y_axis=mtsql_latency_max_list,
            markpoint_opts=opts.MarkPointOpts(
                data=[
                    opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                    opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                ]
            ),
            is_smooth=True,
        )

        ##query latency_avg info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_latency_avg_name)
        mtsql_version_list = []
        mtsql_latency_avg_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_latency_avg_list.append(value_float)
        # graph
        print("result graph for latency_avg!")
        latency_avg_line_max = max(mtsql_latency_avg_list)
        latency_avg_line_max_global = max(latency_avg_line_max, latency_avg_line_max_global)

        latency_avg_line.add_xaxis(xaxis_data=mtsql_version_list)
        latency_avg_line.add_yaxis(
            series_name=mtsql_pt_db_type_full,
            y_axis=mtsql_latency_avg_list,
            markpoint_opts=opts.MarkPointOpts(
                data=[
                    opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                    opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                ]
            ),
            is_smooth=True,
        )

        ##query latency_min info
        param = (mtsql_pt_feature, mtsql_pt_with_feature, mtsql_pt_test_scenarios, mtsql_pt_sub_test_scenarios,
                 mtsql_pt_db_type, mtsql_pt_test_case, mtsql_pt_test_batch_no,
                 mtsql_pt_os_name, mtsql_pt_machine_hardware_name, mtsql_pt_deploy_type,
                 mtsql_latency_min_name)
        mtsql_version_list = []
        mtsql_latency_min_list = []
        cursor.execute(sql, param)
        results = cursor.fetchall()
        for row in results:
            mtsql_version = row[0]
            value = row[1]
            mtsql_version_list.append(mtsql_version)
            value = value.strip();
            value_float = 0.0
            if len(value) > 0:
                value_float = float(value)
            mtsql_latency_min_list.append(value_float)
        # graph
        print("result graph for latency_min!")
        latency_min_line_max = max(mtsql_latency_min_list)
        latency_min_line_max_global = max(latency_min_line_max, latency_min_line_max_global)

        latency_min_line.add_xaxis(xaxis_data=mtsql_version_list)
        latency_min_line.add_yaxis(
            series_name=mtsql_pt_db_type_full,
            y_axis=mtsql_latency_min_list,
            markpoint_opts=opts.MarkPointOpts(
                data=[
                    opts.MarkPointItem(type_="max", name="最大值", symbol_size=[150, 30]),
                    opts.MarkPointItem(type_="min", name="最小值", symbol_size=[150, 30]),
                ]
            ),
            is_smooth=True,
        )

    except Exception as e:
        print("result latency summary_by_test_case graph catch exception!")
        print(e)

db.close()

percentile_line_max_global = int(percentile_line_max_global / 10) * 10
percentile_line_max_global = percentile_line_max_global + 100
latency_max_line_max_global = int(latency_max_line_max_global / 10) * 10
latency_max_line_max_global = latency_max_line_max_global + 100
latency_avg_line_max_global = int(latency_avg_line_max_global / 10) * 10
latency_avg_line_max_global = latency_avg_line_max_global + 100
latency_min_line_max_global = int(latency_min_line_max_global / 10) * 10
latency_min_line_max_global = latency_min_line_max_global + 100

percentile_line.set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                          name_gap=5,
                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                 yaxis_opts=opts.AxisOpts(
                     name="percentile999(ms)",
                     max_=percentile_line_max_global,
                     min_=percentile_line_min_global,
                     max_interval=50000,
                     min_interval=10000),
                 )

latency_max_line.set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                          name_gap=5,
                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                 yaxis_opts=opts.AxisOpts(
                     name="latency_max(ms)",
                     max_=latency_max_line_max_global,
                     min_=latency_max_line_min_global,
                     max_interval=50000,
                     min_interval=10000),
                 )

latency_avg_line.set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                          name_gap=5,
                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                 yaxis_opts=opts.AxisOpts(
                     name="latency_avg(ms)",
                     max_=latency_avg_line_max_global,
                     min_=latency_avg_line_min_global,
                     max_interval=50000,
                     min_interval=10000),
                 )

latency_min_line.set_global_opts(xaxis_opts=opts.AxisOpts(name="并发数",
                                          name_gap=5,
                                          axislabel_opts=opts.LabelOpts(rotate=30)),
                 yaxis_opts=opts.AxisOpts(
                     name="latency_min(ms)",
                     max_=latency_min_line_max_global,
                     min_=latency_min_line_min_global,
                     max_interval=50000,
                     min_interval=10000),
                 )

grid = (
    Grid(init_opts=opts.InitOpts(width="1500px", height="800px"))
        .add(percentile_line,
             grid_opts=opts.GridOpts(pos_top="10%", pos_bottom="55%", pos_left="5%", pos_right="55%"))
        .add(latency_max_line,
             grid_opts=opts.GridOpts(pos_top="10%", pos_bottom="55%", pos_left="55%", pos_right="5%"))
        .add(latency_avg_line,
             grid_opts=opts.GridOpts(pos_top="55%", pos_bottom="10%", pos_left="5%", pos_right="55%"))
        .add(latency_min_line,
             grid_opts=opts.GridOpts(pos_top="55%", pos_bottom="10%", pos_left="55%", pos_right="5%"))
        .render(graph_file_name)
)

